package com.yellowpage.bigdata.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.map.LinkedMap;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.yellowpage.white.common.Constants;
import com.yellowpage.white.common.PageRender;
import com.yellowpage.white.dao.QueryAssistantSQL;
import com.yellowpage.white.service.BaseServiceImpl;
import com.yellowpage.bigdata.data.O2oLogSumDay;
import com.yellowpage.bigdata.dao.O2oLogSumDayDao;

public class O2oLogSumDayServiceImpl extends BaseServiceImpl implements O2oLogSumDayService {
	  private O2oLogSumDayDao o2oLogSumDayDao;
	
	  public void setO2oLogSumDayDao(O2oLogSumDayDao o2oLogSumDayDao) {
		  this.o2oLogSumDayDao = o2oLogSumDayDao;
	  }
	
	
	  /* Smooth generate Method */ 
	  public O2oLogSumDay getO2oLogSumDay(Long id){
	      return o2oLogSumDayDao.get(id);
	  }
	  
    public List<O2oLogSumDay> getAllO2oLogSumDay(){
        return o2oLogSumDayDao.getAll();
    }


	//做一个内部调用函数，简化代码
	private QueryAssistantSQL returnsqlAssistant(Map queryMap)
	{

		String t1 = queryMap.get("query_logDate_end").equals("") ? "" : " AND A.DAY_ID <= '"+queryMap.get("query_logDate_end")+"'" ;
		String t2 = queryMap.get("logDate").equals("") ? "" : "  AND A.DAY_ID >= '"+queryMap.get("logDate")+"'" ;
		String t3 = queryMap.get("o2oId").equals("") ? "" : "    AND A.O2O_ID =  '"+queryMap.get("o2oId")+"' ";

		//System.out.println("query_logDate_end:" +t1+"logDate : "+t2+"  o2oId: " +t3);

		//使用新数数据库ta_o2o_id_day 后的结构
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL
				(

						" SELECT  DAY_ID, O2O_ID, YES_UV, UV, YES_PV, PV, TOTAL_UV, TOTAL_PV, "+

								" (SELECT X.`NAME` FROM O2O_CODE X WHERE X.ID = A.O2O_ID) AS O2O_NAME " +

								" FROM  TA_O2O_ID_DAY  A  WHERE 1=1  "


				)
				.addCondition(" and A.DAY_ID >= :logDate ")
				.addCondition(" and A.DAY_ID <= :query_logDate_end ")
				.addCondition(" and A.O2O_ID = :o2oId ") ;
		sqlAssistant.setTail("ORDER BY DAY_ID,PV DESC");

		return  sqlAssistant;

	}


    public List<Map> queryO2oLogSumDayByPage(Map queryMap, PageRender pageRender) {

		QueryAssistantSQL sqlAssistant = returnsqlAssistant(queryMap);

		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);

		return resultList;
	}

	public List<Map> queryO2oLogSumDayByPage(Map queryMap)

	{
		QueryAssistantSQL sqlAssistant = returnsqlAssistant(queryMap);

		//返回全量
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(), queryMap);

		return resultList;
	}



	public List<Map> queryO2oLogSumDayChannelByPage(Map queryMap, PageRender pageRender) {
     
    	String t1 = queryMap.get("query_logDate_end").equals("") ? "" : " AND A.DAY_ID <= '"+queryMap.get("query_logDate_end")+"'" ;
		String t2 = queryMap.get("logDate").equals("") ? "" : "  AND A.DAY_ID >= '"+queryMap.get("logDate")+"'" ;
    	String t3 = queryMap.get("o2oId").equals("") ? "" : "    AND A.O2O_ID =  '"+queryMap.get("o2oId")+"' ";
    	String t4 = queryMap.get("channelId").equals("") ? "" : "    AND A.CHANNEL_NO =  '"+queryMap.get("channelId")+"' ";
    	//System.out.println("query_logDate_end:" +t1+"logDate : "+t2+"  o2oId: " +t3 + "channelId : " +  t4 );

		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(
//		"SELECT  DAY_ID, O2O_ID, O2O_NAME , CHANNEL_NO , CHANNEL_NAME, SUM(PV_VAL ) PV_VAL, SUM(YES_PV_VAL ) YES_PV_VAL, "
//		+"        SUM(UV_VAL ) UV_VAL, SUM(YES_UV_VAL ) YES_UV_VAL,  "
//		+"        SUM(SUM_PV_VAL ) SUM_PV_VAL,SUM(SUM_UV_VAL ) SUM_UV_VAL "
//		+"FROM ( "
//		+"SELECT  "
//		+"DAY_ID , "
//		+"O2O_ID ,  "
//		+"(SELECT X.`NAME` FROM O2O_CODE X WHERE X.ID = A.O2O_ID) AS O2O_NAME , "
//		+"CHANNEL_NO, "
//		+"(SELECT  NAME FROM OMC_DIC_TABLE X  WHERE  dic_name='O2O_LOG_CHANNEL_NO' AND  X.VALUE= A.CHANNEL_NO) AS CHANNEL_NAME , "
//		+" COUNTS AS PV_VAL , "
////		+" (SELECT  Y.`COUNTS`   FROM O2O_LOG_SUM_DAY Y WHERE Y.O2O_ID = A.O2O_ID AND  Y.IS_IMEI = '0' AND Y.IS_TOTAL = '0' AND Y.CHANNEL_NO <> 'A' AND Y.DAY_ID = DATE_FORMAT( DATE_SUB(A.DAY_ID,INTERVAL 1 DAY) , \"%Y%m%d\") ) AS YES_PV_VAL , "
//        +" (SELECT  Y.`COUNTS`   FROM O2O_LOG_SUM_DAY Y WHERE Y.O2O_ID = A.O2O_ID AND  Y.IS_IMEI = '0' AND Y.IS_TOTAL = '0' AND Y.CHANNEL_NO  = A.CHANNEL_NO AND Y.DAY_ID = DATE_FORMAT( DATE_SUB(A.DAY_ID,INTERVAL 1 DAY) , \"%Y%m%d\") ) AS YES_PV_VAL , "
//        +"0 AS UV_VAL ,  "      
//		+"0 AS YES_UV_VAL , "
//		+"0 AS SUM_PV_VAL , "
//		+"0 AS SUM_UV_VAL   "
//		+"FROM  O2O_LOG_SUM_DAY  A  WHERE 1=1  "
//		+"AND A.IS_IMEI = '0' AND A.IS_TOTAL = '0' AND A.CHANNEL_NO <> 'A' "
//		+ t1 + t2 + t3 + t4
//		+" UNION ALL "		
//		+"SELECT  "
//		+"DAY_ID , "
//		+"O2O_ID ,  "
//		+"(SELECT X.`NAME` FROM O2O_CODE X WHERE X.ID = A.O2O_ID) AS O2O_NAME , "
//		+"CHANNEL_NO, "
//		+"(SELECT  NAME FROM OMC_DIC_TABLE X  WHERE  dic_name='O2O_LOG_CHANNEL_NO' AND  X.VALUE= A.CHANNEL_NO) AS CHANNEL_NAME , "
//		+" 0 AS PV_VAL , "
//		+"  0  AS YES_PV_VAL , "
//		+" COUNTS AS UV_VAL, "
////		+" (SELECT   Y.`COUNTS`   FROM O2O_LOG_SUM_DAY Y WHERE Y.O2O_ID = A.O2O_ID AND  Y.IS_IMEI = '1' AND Y.IS_TOTAL = '0' AND Y.CHANNEL_NO <> 'A' AND Y.DAY_ID = DATE_FORMAT( DATE_SUB(A.DAY_ID,INTERVAL 1 DAY) , \"%Y%m%d\") ) AS YES_UV_VAL , "
//		+" (SELECT   Y.`COUNTS`   FROM O2O_LOG_SUM_DAY Y WHERE Y.O2O_ID = A.O2O_ID AND  Y.IS_IMEI = '1' AND Y.IS_TOTAL = '0' AND Y.CHANNEL_NO  = A.CHANNEL_NO AND Y.DAY_ID = DATE_FORMAT( DATE_SUB(A.DAY_ID,INTERVAL 1 DAY) , \"%Y%m%d\") ) AS YES_UV_VAL , "
//		+"0 AS SUM_PV_VAL ,"
//		+"0 AS SUM_UV_VAL  "
//		+"FROM  O2O_LOG_SUM_DAY  A  WHERE 1=1 " 
//		+"AND A.IS_IMEI = '1' AND A.IS_TOTAL = '0' AND A.CHANNEL_NO <> 'A' "
//		+ t1 + t2 + t3 + t4
//		+" UNION ALL "
//		+"SELECT  "
//		+"DAY_ID , "
//		+"O2O_ID ,  "
//		+"(SELECT X.`NAME` FROM O2O_CODE X WHERE X.ID = A.O2O_ID) AS O2O_NAME ,"
//		+"CHANNEL_NO, "
//		+"(SELECT  NAME FROM OMC_DIC_TABLE X  WHERE  dic_name='O2O_LOG_CHANNEL_NO' AND  X.VALUE= A.CHANNEL_NO) AS CHANNEL_NAME , "
//		+" 0 AS PV_VAL ,"
//		+" 0 AS YES_PV_VAL,"
//		+" 0 AS UV_VAL ,"
//		+" 0 AS YES_UV_VAL,"
//		+"COUNTS AS SUM_PV_VAL ,"
//		+"0 AS SUM_UV_VAL  "
//		+" FROM  O2O_LOG_SUM_DAY  A  WHERE 1=1  "
//		+" AND A.IS_IMEI = '0' AND A.IS_TOTAL = '1' AND A.CHANNEL_NO <> 'A'"
//		+ t1 + t2 + t3 + t4
//		+" UNION ALL "
//		+"SELECT  "
//		+"DAY_ID , "
//		+"O2O_ID ,  "
//		+"(SELECT X.`NAME` FROM O2O_CODE X WHERE X.ID = A.O2O_ID) AS O2O_NAME ,"
//		+"CHANNEL_NO, "
//		+"(SELECT  NAME FROM OMC_DIC_TABLE X  WHERE  dic_name='O2O_LOG_CHANNEL_NO' AND  X.VALUE= A.CHANNEL_NO) AS CHANNEL_NAME , "
//		+" 0 AS PV_VAL ,"
//		+" 0 AS YES_PV_VAL,"
//		+" 0 AS UV_VAL ,"
//		+" 0 AS YES_UV_VAL,"
//		+" 0 AS SUM_PV_VAL ,"
//		+" COUNTS AS SUM_UV_VAL  "
//		+"FROM  O2O_LOG_SUM_DAY  A  WHERE 1=1  "
//		+"AND A.IS_IMEI = '1' AND A.IS_TOTAL = '1' AND A.CHANNEL_NO <> 'A'"
//		+ t1 + t2 + t3 + t4
//		+" )  S  WHERE 1=1 " )  
//		 .addCondition(" and S.DAY_ID >= :logDate ")
//		 .addCondition(" and S.DAY_ID <= :query_logDate_end ")
//		 .addCondition(" and S.O2O_ID = :o2oId ")  
//		 .addCondition(" and S.CHANNEL_NO = :channelId ") ;
//		sqlAssistant.setTail(" GROUP BY  S.DAY_ID, S.O2O_ID, S.O2O_NAME,S.CHANNEL_NO,S.CHANNEL_NAME ");
			    "SELECT  DAY_ID, O2O_ID, O2O_NAME ,CHANNEL_NO,CHANNEL_NAME, SUM(IFNULL(PV_VAL,0) ) PV_VAL, SUM(IFNULL(YES_PV_VAL,0) ) YES_PV_VAL, "
				  +"SUM(IFNULL(UV_VAL ,0)) UV_VAL, SUM(IFNULL(YES_UV_VAL,0) ) YES_UV_VAL,  "
				  +"SUM(IFNULL(SUM_PV_VAL,0) ) SUM_PV_VAL,SUM(IFNULL(SUM_UV_VAL ,0)) SUM_UV_VAL "
					+"FROM ( "
					+"SELECT  "
					+"DAY_ID , "
					+"O2O_ID ,  "
					+"(SELECT X.`NAME` FROM O2O_CODE X WHERE X.ID = A.O2O_ID) AS O2O_NAME , "
					+"CHANNEL_NO, "
					+"(SELECT  NAME FROM OMC_DIC_TABLE X  WHERE  dic_name='O2O_LOG_CHANNEL_NO' AND  X.VALUE= A.CHANNEL_NO) AS CHANNEL_NAME , "
					+"CASE WHEN IS_IMEI = '0' AND IS_TOTAL = '0' THEN COUNTS  END AS PV_VAL , "
					+"CASE WHEN IS_IMEI = '0' AND IS_TOTAL = '0' THEN (SELECT   Y.`COUNTS`   FROM O2O_LOG_SUM_DAY Y WHERE Y.O2O_ID = A.O2O_ID AND  Y.IS_IMEI = '0' AND Y.IS_TOTAL = '0' AND Y.CHANNEL_NO  = A.CHANNEL_NO AND Y.DAY_ID = DATE_FORMAT( DATE_SUB(A.DAY_ID,INTERVAL 1 DAY) , \"%Y%m%d\") ) END AS YES_PV_VAL , "
					+"CASE WHEN IS_IMEI = '1' AND IS_TOTAL = '0' THEN COUNTS  END AS UV_VAL ,   "
					+"CASE WHEN IS_IMEI = '1' AND IS_TOTAL = '0' THEN (SELECT   Y.`COUNTS`   FROM O2O_LOG_SUM_DAY Y WHERE Y.O2O_ID = A.O2O_ID AND  Y.IS_IMEI = '1' AND Y.IS_TOTAL = '0' AND Y.CHANNEL_NO =  A.CHANNEL_NO AND Y.DAY_ID = DATE_FORMAT( DATE_SUB(A.DAY_ID,INTERVAL 1 DAY) , \"%Y%m%d\") ) END AS YES_UV_VAL , "
					+"CASE WHEN IS_IMEI = '0' AND IS_TOTAL = '1' THEN COUNTS  END AS SUM_PV_VAL , "
					+"CASE WHEN IS_IMEI = '1' AND IS_TOTAL = '1' THEN COUNTS  END AS SUM_UV_VAL   "
					+"FROM  O2O_LOG_SUM_DAY  A  WHERE 1=1   AND A.CHANNEL_NO <> 'A' "
					+ t1 + t2 + t3 + t4 
			        +") S  WHERE 1=1 " )  
					 .addCondition(" and S.DAY_ID >= :logDate ")
					 .addCondition(" and S.DAY_ID <= :query_logDate_end ")
					 .addCondition(" and S.O2O_ID = :o2oId ")
					 .addCondition(" and S.CHANNEL_NO = :channelId ") ;
					 sqlAssistant.setTail("  GROUP BY  S.DAY_ID, S.O2O_ID, S.O2O_NAME,S.CHANNEL_NO,S.CHANNEL_NAME ORDER BY S.DAY_ID");
					 
    	List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);

		return resultList;
		
    }
    
	@Override
	public Map getO2oIdMap() {
		Map dataSourceMap = new LinkedMap();
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(" SELECT a.name as NAME, a.ID as VALUE  FROM O2O_CODE a  ");
		sqlAssistant.setTail("order by (a.ID+0)");
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				new HashMap());
		for (int i = 0; i < resultList.size(); i++) {
			dataSourceMap.put(resultList.get(i).get("VALUE"), resultList.get(i)
					.get("VALUE") + "-" + resultList.get(i).get("NAME"));
		}
		return dataSourceMap;
	}
    
	@Override
	public Map getO2oChannelMap() {
		Map dataSourceMap = new LinkedMap();
		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(" SELECT a.name as NAME, a.VALUE as VALUE  FROM OMC_DIC_TABLE a where dic_name='O2O_LOG_CHANNEL_NO' ");
		sqlAssistant.setTail("order by (a.VALUE+0)");
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				new HashMap());
		for (int i = 0; i < resultList.size(); i++) {
			dataSourceMap.put(resultList.get(i).get("VALUE"), resultList.get(i)
					.get("VALUE") + "-" + resultList.get(i).get("NAME"));
		}
		return dataSourceMap;
	}
    
 	
	
//    public void saveO2oLogSumDay(O2oLogSumDay o2oLogSumDay){
//        o2oLogSumDayDao.save(o2oLogSumDay);
//    }
//    
//	  public void updateO2oLogSumDay(O2oLogSumDay o2oLogSumDay){
//	      o2oLogSumDayDao.update(o2oLogSumDay);
//	  }
//	  
//	  public void delete(List deleteList){
//	      o2oLogSumDayDao.remove(deleteList);
//	  }
	  
	  
	 
	
}	